package com.project.controller;


import com.project.common.Result;
import com.project.entity.db.Col;
import com.project.entity.db.TableCreationRequest;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import io.swagger.annotations.ApiParam;
import org.springframework.web.bind.annotation.*;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

@RestController
@RequestMapping("test")
@Api(tags="数据库相关接口")
public class DBController {

    @GetMapping("/testGetMapping")
    @ResponseBody
    public String testMethod(){

        return "testGetMapping";
    }


    @GetMapping("/createDB")
    @ApiOperation("新增数据库")
    public Result<String> createDB(String url, String userName, String password, String databaseName , String charset , String collation ){
        // 数据库连接信息
//        String url = "jdbc:mysql://39.101.64.201:3308/mysql";
//        String username = "root";
//        String password = "12345679abcABC";

        try (Connection conn = DriverManager.getConnection(url, userName, password);
             Statement stmt = conn.createStatement()) {

            String createDatabaseQuery = "CREATE DATABASE " + databaseName + " CHARACTER SET " + charset + " COLLATE " + collation;
            stmt.executeUpdate(createDatabaseQuery);
            System.out.println("Database " + databaseName + " created successfully.");

            return Result.success("数据库操作成功");
        } catch (SQLException e) {
            e.printStackTrace();
        }


        return Result.error("数据库操作错误");
    }


    @GetMapping("/showDatabases")
    @ApiOperation("查找所有数据库")
    public Result<List<String>> showDatabases(String url,String userName,String password){
//        String url = "jdbc:mysql://39.101.64.201:3308/mysql";
//        String username = "root";
//        String password = "12345679abcABC";

        List<String> databases=new ArrayList<>();
        try (Connection conn = DriverManager.getConnection(url, userName, password);
             Statement stmt = conn.createStatement()) {
            ResultSet rs = stmt.executeQuery("SHOW DATABASES");
            while (rs.next()) {
                String var = rs.getString(1);
                System.out.println(var);
                databases.add(var);
            }

            return Result.success(databases);
        } catch (SQLException e) {
            e.printStackTrace();


        }

        return Result.error("连接出错");
    }


    @GetMapping("/character")
    @ApiOperation("查找支持的字符集")
//    public Result<List<String>> character(){
    public Result<List<String>> character(String url,String userName,String password){
//        String url = "jdbc:mysql://39.101.64.201:3308/mysql";
//        String username = "root";
//        String password = "12345679abcABC";



        List<String> result=new ArrayList<>();
        try (Connection conn = DriverManager.getConnection(url, userName, password);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SHOW CHARACTER SET")) {

            while (rs.next()) {
                String charset = rs.getString("Charset");
                result.add(charset);
            }
            return Result.success(result);

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return Result.error("数据库操作错误");
    }


    @GetMapping("/collation")
    @ApiOperation("查找字符集所支持的排序规则")
//    public Result<List<String>> collation(){
    public Result<List<String>> collation(String url,String userName,String password,String charset){
//        String url = "jdbc:mysql://39.101.64.201:3308/mysql";
//        String username = "root";
//        String password = "12345679abcABC";

//        String charset = "armscii8"; // 指定要查询的字符集

        List<String> result=new ArrayList<>();
        try (Connection conn = DriverManager.getConnection(url, userName, password);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SHOW COLLATION WHERE Charset = '" + charset + "'")) {

            System.out.println("Sorting Rules for Charset " + charset + ":");
            System.out.println("--------------------------------------");

            while (rs.next()) {
                String collation = rs.getString("Collation");
                result.add(collation);
            }

            return Result.success(result);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return Result.error("数据库操作错误");
    }


    @GetMapping("/deleteDB")
    @ApiOperation("删除数据库")
//    public Result<List<String>> collation(){
    public Result<String> deleteDB(String url,String userName,String password,String databaseName){
//        String url = "jdbc:mysql://39.101.64.201:3308/mysql";
//        String username = "root";
//        String password = "12345679abcABC";

//        String charset = "armscii8"; // 指定要查询的字符集

        List<String> result=new ArrayList<>();
        try (Connection conn = DriverManager.getConnection(url, userName, password);
             Statement stmt = conn.createStatement()) {

            String dropDatabaseQuery = "DROP DATABASE " + databaseName;
            stmt.executeUpdate(dropDatabaseQuery);
            System.out.println("Database " + databaseName + " deleted successfully.");

            return Result.success("数据库操作成功");
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return Result.error("数据库操作错误");
    }


    /**
     * 根据请求创建数据库表。
     *
     * @param request 包含表创建请求信息的对象，包括表名、列信息、主键、外键和约束等。
     * @return 返回操作结果，成功则返回"数据库操作成功"，失败则返回"数据库操作失败"。
     */
    @PostMapping("/createTable")
    @ApiOperation(value = "创建表", notes = "根据提供的表结构信息创建一个新的数据库表")
    public Result<String> createTable(@RequestBody @ApiParam(name = "表创建请求", value = "包含表名、列信息、主键、外键和约束等详细数据的请求体") TableCreationRequest request) {
        // JDBC连接信息
//        String url = "jdbc:mysql://39.101.64.201:3308/mysql";
//        String username = "root";
//        String password = "12345679abcABC";

        String url = request.getUrl();
        String username = request.getUsername();
        String password = request.getPassword();

        try (Connection conn = DriverManager.getConnection(url, username, password);
             Statement stmt = conn.createStatement()) {

            // 构建创建表的SQL查询语句
            String str="use "+request.getDatabaseName()+";";
            stmt.executeUpdate(str);

            StringBuilder createTableQuery = new StringBuilder();
            createTableQuery.append("CREATE TABLE ").append(request.getTableName()).append(" (");

            for (Col var:request.getColumns()) {
                createTableQuery.append(var.getColumnName()).append(" ").append(var.getType());
                if(var.getLength()!=null){// 补充长度
                    createTableQuery.append("(").append(var.getLength()).append(")");
                }
                if (var.getChoose()!=null){// 补充是否非空约束
                    createTableQuery.append(" ").append(var.getChoose());
                }
                if (var.getNote()!=null){// 补充备注
                    createTableQuery.append(" COMMENT '").append(var.getNote()).append("'");
                }

                createTableQuery.append(", ");

            }

            // 补充主键
            if (request.getPrimaryKey() != null && !request.getPrimaryKey().isEmpty()) {
                createTableQuery.append("PRIMARY KEY (").append(request.getPrimaryKey()).append("), ");
            }
            // 补充外键
            if (request.getForeignKey() != null && !request.getForeignKey().isEmpty()) {
                createTableQuery.append("FOREIGN KEY (").append(request.getForeignKey()).append("), ");
            }
            // 补充约束
            if (request.getConstraints() != null && !request.getConstraints().isEmpty()) {
                createTableQuery.append(request.getConstraints()).append(", ");
            }

            // 移除最后一行的逗号和空格
            createTableQuery.delete(createTableQuery.length() - 2, createTableQuery.length()); // Remove the last comma and space
            createTableQuery.append(")");

            // 执行创建表语句
            System.out.println(createTableQuery.toString());
            stmt.executeUpdate(createTableQuery.toString());
            System.out.println("Table " + request.getTableName() + " created successfully in database " + request.getDatabaseName());
            return Result.success(str+createTableQuery);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return Result.error("数据库操作失败");
    }


    @GetMapping("/getTableSql")
    @ApiOperation(value = "查找表的sql语句")
    public Result<String> getTableSql() {
        // JDBC连接信息
        String url = "jdbc:mysql://39.101.64.201:3308/hmdp";
        String userName = "root";
        String password = "12345679abcABC";

        try (Connection conn = DriverManager.getConnection(url, userName, password);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SHOW CREATE TABLE tb_user")) {

            String sql ="";
            if(rs.next()){
                sql= rs.getString("Create Table");
            }

            return Result.success(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return Result.error("数据库操作失败");
    }



}
